package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Bean.Car;
import Bean.User;
import Bean.UserAndCar;
import sql.JDBCUtil;

public class User_CarDao {
	// 通过用户名查找所属车辆
	public List<UserAndCar> selectAllCarOfUser(String username) throws Exception {
		List<UserAndCar> list = new ArrayList<UserAndCar>();
		UserAndCar userAndCar = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection con = null;
		String sql = "select c.username as username, c.car_id as car_id, s.createTime as createTime from user_car c left join car s on c.car_id = s.car_id where username = ?;";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			while(rs.next()) {
				userAndCar = new UserAndCar();
				userAndCar.setUsername(rs.getString("username"));
				userAndCar.setCar_id(rs.getString("car_id"));
				userAndCar.setCreateTime(rs.getString("createTime"));
				list.add(userAndCar);
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return list;
	}
	public boolean contextUserAndCar(String username, String car_id) throws SQLException {
		// 连接车辆与用户，添加车辆必须执行这个方法
		CarDao carDao = new CarDao();
		UserDao userDao = new UserDao();
		Car car = null;
		User user = null;
		// ResultSet rs = null;
		PreparedStatement ps = null;
		Connection con = null;
		String sql = "insert into user_car value(?, ?);";
		try {
			car = carDao.selectByCar_id(car_id);
			user = userDao.selectByUsername(username);
			if(car == null || user == null)
				return false;
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, car_id);
			ps.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.close(null, ps, con);
		}
		return true;
	}
	public List<UserAndCar> selectUserAndCarsByCreateTime(String createTime) throws Exception {
		List<UserAndCar> list = new ArrayList<UserAndCar>();
		UserAndCar userAndCar = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection con = null;
		String sql = "select c.username as username, c.car_id as car_id, s.createTime as createTime from user_car c left join car s on c.car_id = s.car_id where createTime = ?;";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, createTime);
			rs = ps.executeQuery();
			while(rs.next()) {
				userAndCar = new UserAndCar();
				userAndCar.setUsername(rs.getString("username"));
				userAndCar.setCar_id(rs.getString("car_id"));
				userAndCar.setCreateTime(rs.getString("createTime"));
				list.add(userAndCar);
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return list;
	}
	public UserAndCar selectOneCar(String car_id) throws Exception {
		UserAndCar userAndCar = new UserAndCar();
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection con = null;
		String sql = "select c.username as username, c.car_id as car_id, s.createTime as createTime from user_car c left join car s on c.car_id = s.car_id where c.car_id = ?;";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, car_id);
			rs = ps.executeQuery();
			if(rs.next()) {
				userAndCar.setUsername(rs.getString("username"));
				userAndCar.setCar_id(rs.getString("car_id"));
				userAndCar.setCreateTime(rs.getString("createTime"));
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return userAndCar;
	}
}
